package com.ridbor001.demo.service;

import com.ridbor001.demo.bean.RDBCourse;
import com.ridbor001.demo.bean.RDBGrade;
import com.ridbor001.demo.bean.RDBStudent;
import com.ridbor001.demo.bean.RDBStudentCourse;
import com.ridbor001.demo.dao.*;
import org.springframework.stereotype.Service;

import java.sql.SQLException;
import java.util.*;
import java.util.stream.Collectors;


@Service
public class RDBGradeService {
    RDBStudentDAO  rdbStudentDAO = new RDBStudentDAO();
    RDBCourseDAO  rdbCourseDAO = new RDBCourseDAO();
    RDBGradeDAO  rdbGradeDAO = new RDBGradeDAO();
    RDBStudentCourseDAO rdbStudentCourseDAO = new RDBStudentCourseDAO();
    // 获取所有学生课程关联数据（包含完整实体）
    public   List<RDBStudentCourse> getAllRelations() throws SQLException {
        // 1. 获取所有学生课程关系记录
        List<StudentCourseRelation> relations = rdbStudentCourseDAO.getAllStudentCourseRelations();

        // 2. 收集所有学生ID、课程ID和成绩ID
        Set<String> studentIds = relations.stream()
                .map(StudentCourseRelation::getStudentId)
                .collect(Collectors.toSet());

        Set<String> courseIds = relations.stream()
                .map(StudentCourseRelation::getCourseId)
                .collect(Collectors.toSet());

        Set<String> gradeIds = relations.stream()
                .map(StudentCourseRelation::getGradeId)
                .filter(Objects::nonNull)
                .collect(Collectors.toSet());

        // 3. 批量获取相关实体
        Map<String, RDBStudent> studentMap = rdbStudentDAO.getStudentsByIds(studentIds);
        Map<String, RDBCourse> courseMap = rdbCourseDAO.getCoursesByIds(courseIds);
        Map<String, RDBGrade> gradeMap = rdbGradeDAO.getGradesByIds(gradeIds);

        // 4. 构建完整的学生课程关系对象
        return relations.stream()
                .map(relation -> {
                    RDBStudent student = studentMap.get(relation.getStudentId());
                    RDBCourse course = courseMap.get(relation.getCourseId());
                    RDBGrade grade = relation.getGradeId() != null ?
                            gradeMap.get(relation.getGradeId()) : null;

                    return new RDBStudentCourse(student, course, grade);
                })
                .collect(Collectors.toList());
    }

    // 搜索功能实现
    public List<RDBStudentCourse> search(String keyword) throws SQLException {
        // 1. 搜索匹配的学生和课程
        List<RDBStudent> matchedStudents = rdbStudentDAO.searchStudents(keyword);
        List<RDBCourse> matchedCourses = rdbCourseDAO.searchCourses(keyword);

        // 2. 收集相关ID
        Set<String> studentIds = matchedStudents.stream()
                .map(RDBStudent::getStudentId)
                .collect(Collectors.toSet());

        Set<String> courseIds = matchedCourses.stream()
                .map(RDBCourse::getCourseId)
                .collect(Collectors.toSet());

        // 3. 获取相关关系记录
        List<StudentCourseRelation> relations = rdbStudentCourseDAO.getRelationsByStudentOrCourse(
                studentIds, courseIds
        );

        // 4. 批量获取相关实体
        Set<String> allStudentIds = relations.stream()
                .map(StudentCourseRelation::getStudentId)
                .collect(Collectors.toSet());

        Set<String> allCourseIds = relations.stream()
                .map(StudentCourseRelation::getCourseId)
                .collect(Collectors.toSet());

        Set<String> gradeIds = relations.stream()
                .map(StudentCourseRelation::getGradeId)
                .filter(Objects::nonNull)
                .collect(Collectors.toSet());

        Map<String, RDBStudent> studentMap = rdbStudentDAO.getStudentsByIds(allStudentIds);
        Map<String, RDBCourse> courseMap = rdbCourseDAO.getCoursesByIds(allCourseIds);
        Map<String, RDBGrade> gradeMap = rdbGradeDAO.getGradesByIds(gradeIds);

        // 5. 构建完整的学生课程关系对象
        return relations.stream()
                .map(relation -> {
                    RDBStudent student = studentMap.get(relation.getStudentId());
                    RDBCourse course = courseMap.get(relation.getCourseId());
                    RDBGrade grade = relation.getGradeId() != null ?
                            gradeMap.get(relation.getGradeId()) : null;

                    return new RDBStudentCourse(student, course, grade);
                })
                .collect(Collectors.toList());
    }
    private List<RDBStudentCourse> getStudentRelations(RDBStudent student) throws SQLException {
        List<RDBStudentCourse> relations = new ArrayList<>();

        // 获取学生的课程列表
        List<RDBCourse> courses = rdbCourseDAO.getCoursesByStudent(student.getStudentId());

        // 提前获取所有相关成绩（优化性能）
        Set<String> courseIds = courses.stream()
                .map(RDBCourse::getCourseId)
                .collect(Collectors.toSet());

        Map<String, Optional<RDBGrade>> gradesMap = new HashMap<>();
        for (String courseId : courseIds) {
            Optional<RDBGrade> grade = rdbGradeDAO.findGradeByStudentAndCourse(
                    student.getStudentId(), courseId
            );
            gradesMap.put(courseId, grade);
        }

        // 构建完整的关系对象
        for (RDBCourse course : courses) {
            RDBGrade grade = gradesMap.get(course.getCourseId()).orElse(null);
            relations.add(new RDBStudentCourse(student, course, grade));
        }

        return relations;
    }
    // 获取课程相关关系
    private List<RDBStudentCourse> getCourseRelations(RDBCourse course) throws SQLException {
        List<RDBStudentCourse> relations = new ArrayList<>();

        // 获取选修该课程的学生
        List<RDBStudent> students = rdbStudentDAO.getStudentsByCourse(course.getCourseId());

        // 批量获取这些学生的成绩（优化性能）
        Map<String, RDBGrade> gradesMap = rdbGradeDAO.getGradesByCourseAndStudents(
                course.getCourseId(),
                students.stream().map(RDBStudent::getStudentId).collect(Collectors.toList())
        );

        // 构建完整的关系对象
        for (RDBStudent student : students) {
            RDBGrade grade = gradesMap.get(student.getStudentId());
            relations.add(new RDBStudentCourse(student, course, grade));
        }

        return relations;
    }


// 获取指定学生的所有课程关系
    public List<RDBStudentCourse> getRelationsById(String studentId) throws SQLException {
        // 1. 获取学生对象 - 如果不存在，返回空列表
        Optional<RDBStudent> studentOpt = Optional.ofNullable(rdbStudentDAO.findStudentById(studentId));
        if (!studentOpt.isPresent()) {
            return Collections.emptyList();
        }
        RDBStudent student = studentOpt.get();

        // 2. 获取该学生的所有课程关系
        List<StudentCourseRelation> relations = rdbStudentCourseDAO.getRelationsByStudent(studentId);

        // 3. 收集相关ID
        Set<String> courseIds = relations.stream()
                .map(StudentCourseRelation::getCourseId)
                .collect(Collectors.toSet());

        Set<String> gradeIds = relations.stream()
                .map(StudentCourseRelation::getGradeId)
                .filter(Objects::nonNull)
                .collect(Collectors.toSet());

        // 4. 批量获取相关实体
        Map<String, RDBCourse> courseMap = rdbCourseDAO.getCoursesByIds(courseIds);
        Map<String, RDBGrade> gradeMap = rdbGradeDAO.getGradesByIds(gradeIds);

        // 5. 构建完整的学生课程关系对象
        return relations.stream()
                .map(relation -> {
                    RDBCourse course = courseMap.get(relation.getCourseId());
                    RDBGrade grade = relation.getGradeId() != null ?
                            gradeMap.get(relation.getGradeId()) : null;

                    return new RDBStudentCourse(student, course, grade);
                })
                .collect(Collectors.toList());
    }

    // 删除成绩
    public boolean deleteGrade(String gradeId) throws SQLException {
        // 1. 直接使用Optional对象
        Optional<RDBGrade> gradeOpt = rdbGradeDAO.findGradeById(gradeId);
        if (!gradeOpt.isPresent()) {
            return false;
        }
        RDBGrade grade = gradeOpt.get();

        // 2. 先解除外键引用
        rdbStudentCourseDAO.updateStudentCourseGrade(
                grade.getStudentId(),
                grade.getCourseId(),
                null
        );

// 3. 再删除成绩
        boolean isDeleted = rdbGradeDAO.deleteGrade(gradeId);
        return isDeleted;

    }
    //导入
    public void upsertGrade(RDBGrade grade) throws SQLException {
         rdbGradeDAO.upsertGrade(grade);
        // 2. 关联到学生课程关系
        rdbStudentCourseDAO.updateStudentCourseGrade(
                grade.getStudentId(),
                grade.getCourseId(),
                grade.getGradeId()
        );
    }
    // 添加成绩
    public void addGrade(RDBGrade grade) throws SQLException {
        // 1. 保存成绩到数据库
        rdbGradeDAO.addGrade(grade);

        // 2. 关联到学生课程关系
        rdbStudentCourseDAO.updateStudentCourseGrade(
                grade.getStudentId(),
                grade.getCourseId(),
                grade.getGradeId()
        );
    }

    // 根据ID获取成绩
    public Optional<RDBGrade> getGradeById(String gradeId) throws SQLException {
        return rdbGradeDAO.findGradeById(gradeId);
    }
    public boolean updateGrade(RDBGrade grade) throws SQLException {
        return rdbGradeDAO.updateGrade(grade);
    }
    public Optional<RDBGrade> findGradeByStudentAndCourse(String studentId, String courseId) throws SQLException {
        return rdbGradeDAO.findGradeByStudentAndCourse(studentId,courseId);
    }

}